package com.biz.hunter.dao;

import java.util.Collections;
import java.util.List;

import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.biz.hunter.db.entity.Company;
import com.biz.hunter.dbutil.BaseDAO;


@Repository
public class TempCompanyDAO {
	private static final String SQL_GET_ID = "select * from tb_company_temp  where id = ?";
	private static final String SQL_GET_NAME = "select * from tb_company_temp  where Name = ? limit 0,1";

	private static final String SQL_GET_Employer = "select * from tb_company_temp  where employerId = ?";
	private static final String SQL_GET_ALL = "select * from tb_company_temp ";
	private static final String SQL_GET_ALL_CATEGORY = "select * from tb_company_temp where domainid in "
			+ "(select id from tb_domain where id = ? or parentId = ?)" ;
		
	private static final String SQL_GET_SUBDOMAIN = "select * from tb_company_temp where domain in (";
	
	private static final String SQL_SAVE = "insert into tb_company_temp("
			+ "name,orgnizationCode,licensecode,"
			+ "contact, email,phone,address,"
			+ "approved,createdate,"
			+ "domain,employerId,englishName,scope,property,tags) values(?,?,?"
			+ ",?,?,?,?,"
			+ "?,?,?,?,?,?,?,?)";
	
	private static final String SQL_UPDATE = "update tb_company_temp "
			+ "set name=?,orgnizationCode=?,licensecode=?,"
			+ "contact=?, email=?,phone=?,address=?,approved=?,"
			+ "createdate=?,domain=?,employerId=?, imageUrl =? where id =?";
	
	protected static final String SQL_DELETE = "delete from tb_company_temp where id=?";
	private static final String SQL_GET_ALL_COUNT = "select COUNT(*) from tb_company_temp where 1=1 ";
	private static final String SQL_GET_ALL_WITH_LIMIT = "select * from tb_company_temp where 1=1 ";
	public List<Company> getALL() {

		return BaseDAO.queryForList(Company.class, SQL_GET_ALL);

	}
	
	public List<Company> getDomainCompanies(int domainId) {
			//select * from tb_company where domainid in (select id from tb_domain where id = domainid or parentId = domainid) 
		return BaseDAO.queryForList(Company.class, SQL_GET_ALL_CATEGORY,domainId);
	}
	
	public List<Company> getList(String... domainIds) {

		if (domainIds != null) {
			StringBuilder sql = new StringBuilder(SQL_GET_SUBDOMAIN);
			boolean start = false;
			for (String id : domainIds) {
				if (start) {
					sql.append(",").append(id);
				} else {
					start = true;
					sql.append(id);
				}
			}
			sql.append(")");
			return BaseDAO.queryForList(Company.class, sql.toString());

		} else {
			return Collections.emptyList();
		}
	}
		
	public Company getById(int Id) {
		return  BaseDAO.queryForObject(Company.class, SQL_GET_ID, Id);
	}
	public Company getByName(String name){
		return  BaseDAO.queryForObject(Company.class, SQL_GET_NAME, name);
	}
	public int save(Company com){
		if (com == null)
		{
			return -1;
		}
		Long result = BaseDAO.save(SQL_SAVE, com.getName(),com.getOrgnizationCode(),com.getLicenseCode(),
				com.getContact(),com.getEmail(),com.getPhone(),com.getAddress(),
				com.getApproved(),com.getCreateDate(),com.getDomain(),com.getCreateId(),com.getEnglishName(),
				com.getScope(),com.getProperty(),com.getTags());
		if(result != null){
			return result.intValue();
		}
		return -1;
		
	}
	
	public int update(Company com)
		{
		if (com ==null)
		{
			return -1;
		}
		int result = BaseDAO.execute(SQL_UPDATE, com.getName(),com.getOrgnizationCode(),com.getLicenseCode(),
				com.getContact(),com.getEmail(),com.getPhone(),com.getAddress(),
				com.getApproved(),com.getCreateDate(),com.getDomain(),com.getCreateId(),com.getImage(),com.getId());
		return  result;
	}

	public void deleteById(int Id) {
	
			int result = BaseDAO.execute(SQL_DELETE, Id);
	
		
	}

	public Company getByEmployerId(int hrId) {
		
			return  BaseDAO.queryForObject(Company.class, SQL_GET_Employer, hrId);
	}
	
	public Long getCountOfAll(String name, String phoneNum) {
		String sql = SQL_GET_ALL_COUNT;
		if (StringUtils.hasText(name)){
			sql+=" and name like '%" + name +"%'";
		}
		if (StringUtils.hasText(phoneNum)){
			sql+=" and phone ='" + phoneNum +"' ";
		}
		
		return (Long) BaseDAO.queryOneRowOneCol(sql);
	}
	
	public List<Company> getAllWithLimit(String name, String phoneNum, long start, long length) {
		String sql = SQL_GET_ALL_WITH_LIMIT;
		if (StringUtils.hasText(name)){
			sql+=" and name like '%" + name +"%'";
		}
		if (StringUtils.hasText(phoneNum)){
			sql+=" and phone ='" + phoneNum +"' ";
		}
		sql+=" LIMIT ?, ?";
		return BaseDAO.queryForList(Company.class, sql, start, length);
	}
	
	public int delete(String Id) {

		if (Id != null) {

			int result = BaseDAO.execute(SQL_DELETE, Id);
			return result;
		} else {
			return 0;
		}
	}

	public int updateTemp(Company com) {
		String sql = "update tb_company_temp " +
				" set name=?,orgnizationCode=?,licensecode=?," +
				" contact=?, email=?,phone=?,address=?,approved=?," +
				" createdate=?,domain=?,employerId=?, imageUrl =?,tags = ?,scope = ?,property = ? where id =?";
		if (com ==null)
		{
			return -1;
		}
		int result = BaseDAO.execute(sql, com.getName(),com.getOrgnizationCode(),com.getLicenseCode(),
				com.getContact(),com.getEmail(),com.getPhone(),com.getAddress(),
				com.getApproved(),com.getCreateDate(),com.getDomain(),com.getCreateId(),com.getImage(),
				com.getTags(),com.getScope(),com.getProperty(),com.getId());
		return  result;
	}

	public Company getByEmployerIdAndName(int employerId, String companyName) {
		String sql = "select * from tb_company_temp  where employerId = ? and name = ?";
		return  BaseDAO.queryForObject(Company.class, sql, employerId,companyName);
	}
}
